/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package ProjectMgtSystem.Bean;

import javax.servlet.http.HttpServletRequest;

import org.apache.struts.action.ActionErrors;
import org.apache.struts.action.ActionMapping;
import org.apache.struts.action.ActionMessage;
import java.util.*;
import java.sql.*;
import ProjectMgtSystem.Entities.*;
import ProjectMgtSystem.Utilities.*;
import com.sun.xml.internal.bind.v2.runtime.unmarshaller.IntArrayData;

/**
 *
 * @author Nguyen Phu Cuong
 * Created: 2011/07/19
 */
public class AssignmentActionBean extends org.apache.struts.action.ActionForm {

    private String name;
    private int number;
    private String userid;
    private String projectid;
    private String taskid;
    private String engineerid;
    private String engineerid1;
    private String planStartDate;
    private String planEndDate;
    private String startDate;
    private String endDate;
    private String completeRatio;
    
    //private String completeRatio;

    /**
     * @return
     */
    public String getName() {
        return name;
    }

    /**
     * @param string
     */
    public void setName(String string) {
        name = string;
    }

    /**
     * @return
     */
    public int getNumber() {
        return number;
    }

    /**
     * @param i
     */
    public void setNumber(int i) {
        number = i;
    }

    /**
     *
     */
    public AssignmentActionBean() {
        super();
        // TODO Auto-generated constructor stub
    }

    /**
     * This is the action called from the Struts framework.
     * @param mapping The ActionMapping used to select this instance.
     * @param request The HTTP Request we are processing.
     * @return
     */
    public ActionErrors validate(ActionMapping mapping, HttpServletRequest request) {
        ActionErrors errors = new ActionErrors();
        if (getName() == null || getName().length() < 1) {
            errors.add("name", new ActionMessage("error.name.required"));
            // TODO: add 'error.name.required' key to your resources
        }
        return errors;
    }

    /**
     * @return the userid
     */
    public String getUserid() {
        return userid;
    }

    /**
     * @param userid the userid to set
     */
    public void setUserid(String userid) {
        this.userid = userid;
    }

    /**
     * Choose Participated Project
     */
    public ArrayList<Project> viewPartiProj(String userid) throws SQLException {
        ArrayList<Project> arrPro = new ArrayList<Project>();
        try {
            Conn cnn = new Conn();
//            Engineer en = new Engineer();
//            //String userid = getUserid();
//            String select_engineer = "Select * from engineer where engineerid='" + userid + "'";
//            ResultSet rsEngineer = cnn.exec(select_engineer);
//            while (rsEngineer.next()) {
//                en.setEngineerID(userid);
//                en.setFirstName(rsEngineer.getString("firstname"));
//                en.setLastName(rsEngineer.getString("lastname"));
//                en.setMajor(rsEngineer.getString("major"));
//            }

//            String select_project = "Select distinct p.ProjectID, p.projectname, p.teamleaderid, p.description, p.planguage from project p, engineer e"
//                    + " Where p.teamleaderid ='" + userid + "'";

//            ResultSet rsProject = cnn.exec(select_project);
//            if (rsProject != null) {
//                while (rsProject.next()) {
//                    Project pro = new Project();
//                    pro.setProjectID(rsProject.getString("projectid"));
//                    pro.setProjectName(rsProject.getString("projectname"));
//                    pro.setLanguage(rsProject.getString("planguage"));
//                    pro.setDescription(rsProject.getString("description"));
//                    pro.setTeamLeader(en);
//                    //pro.setProject(pro);
//                    arrPro.add(pro);
//                }
             String proc ="call View_ParticipatedProject_ByEnID(?)";
            PreparedStatement prstm = cnn.getPrepareStatement(proc);
            prstm.setString(1, userid);
            ResultSet rsProject = prstm.executeQuery();
            //ResultSet rsProject = cnn.exec(select_project);
            if (rsProject != null) {
                while (rsProject.next()) {
                    Project pro = new Project();
                    pro.setProjectID(rsProject.getString("projectid"));
                    pro.setProjectName(rsProject.getString("projectname"));
                    pro.setLanguage(rsProject.getString("planguage"));
                    pro.setDescription(rsProject.getString("description"));
                    
                    Engineer en = new Engineer();
                    en.setEngineerID(rsProject.getString("teamleaderid"));
                    en.setFirstName(rsProject.getString("firstname"));
                    en.setLastName(rsProject.getString("lastname"));
                    pro.setTeamLeader(en);
                    
                    pro.setCompleteRatio(rsProject.getInt("completeratio")+"");
                    //pro.setProject(pro);
                    arrPro.add(pro);
                }

            } else {
                arrPro = null;
            }
        } catch (Exception e) {
            e.getMessage();
        }
        return arrPro;
    }

    /**
     * @return the projectid
     */
    public String getProjectid() {
        return projectid;
    }

    /**
     * @param projectid the projectid to set
     */
    public void setProjectid(String projectid) {
        this.projectid = projectid;
    }

    /**
     * View task info by project id
     */
    public ArrayList<Assignment> viewTaskByProj(String projectid) {
        ArrayList<Assignment> assList = new ArrayList<Assignment>();
       
       
        try {
            Conn cnn = new Conn();
            
            
            String sql = "Select t.taskid, t.taskname, a.completeratio, a.planstartdate, a.planenddate, a.startdate, a.enddate from task t, assignment a "
                    + "where t.taskid = a.taskid "
                    + "and a.projectid ='"+projectid+"' "
                    + "group by t.taskid";
            ResultSet rs = cnn.exec(sql);
            while (rs.next()) {
                Assignment ass = new Assignment();
                ass.setTaskid(rs.getString("taskid"));
                ass.setTaskName(rs.getString("taskname"));
                ass.setPlanStartDate(rs.getString("planstartdate"));
                ass.setPlanEndDate(rs.getString("planenddate"));
                ass.setStartDate(rs.getString("startdate"));
                ass.setEndDate(rs.getString("enddate"));
                ass.setCompleteRatio(String.valueOf(rs.getInt("completeratio")));
                assList.add(ass);
                
            }

        } catch (Exception e) {
            e.getMessage();
        }
        return assList;
    }

    /**
     * View Assignment info
     */
    public ArrayList<Assignment> viewAssignInfo(String projectid, String taskid){
        ArrayList<Assignment> assList = new ArrayList<Assignment>();
        try{
            Conn cnn = new Conn();
            String sql = "select e.engineerid, e.firstname, e.lastname, a.planstartdate, a.planenddate, a.startdate, a.enddate, a.completeratio from engineer e, assignment a, task t "
                        + "where e.engineerid = a.engineerid "
                        + "and a.taskid = t.taskid "
                        + "and a.taskid = '"+taskid+"' "
                        + "and a.projectid = '"+projectid+"' "
                        + "group by a.taskid";
            ResultSet rs = cnn.exec(sql);
            while(rs.next()){
                Assignment ass = new Assignment();
//                ass.setEngineerid(rs.getString("engineerid"));
//                ass.setEnFirstName(rs.getString("firstname"));
//                ass.setEnLastName(rs.getString("lastname"));
                ass.setPlanStartDate(rs.getString("planstartdate"));
                ass.setPlanEndDate(rs.getString("planenddate"));
                ass.setStartDate(rs.getString("startdate"));
                ass.setEndDate(rs.getString("enddate"));
                ass.setCompleteRatio(rs.getInt("completeratio")+"");
                assList.add(ass);
            }
        }catch(Exception e){
            e.getMessage();
        }
        return assList;
    }
    
    /**
     * view Engineer Assign Info
     */
     public ArrayList<Engineer> viewEngineerAssignInfo(String projectid, String taskid){
        ArrayList<Engineer> enList = new ArrayList<Engineer>();
        try{
            Conn cnn = new Conn();
            String sql = "select e.engineerid, e.firstname, e.lastname from engineer e, assignment a, task t "
                        + "where e.engineerid = a.engineerid "
                        + "and a.taskid = t.taskid "
                        + "and a.taskid = '"+taskid+"' "
                        + "and a.projectid = '"+projectid+"'"
                        + "and a.engineerid <> '0'";
            ResultSet rs = cnn.exec(sql);
            while(rs.next()){
                Engineer en = new Engineer();
                en.setEngineerID(rs.getString("engineerid"));
                en.setFirstName(rs.getString("firstname"));
                en.setLastName(rs.getString("lastname"));
//                ass.setPlanStartDate(rs.getString("planstartdate"));
//                ass.setPlanEndDate(rs.getString("planenddate"));
//                ass.setStartDate(rs.getString("startdate"));
//                ass.setEndDate(rs.getString("enddate"));
//                ass.setCompleteRatio(rs.getInt("completeratio"));
                enList.add(en);
            }
        }
        catch(Exception e){
            e.getMessage();
        }
        return enList;
    }
     
     /**
      * List engineers haven't assigned yet 
      */
     public ArrayList<Engineer> listEngineerNotAssigned(String userid, String projectid, String taskid){
         ArrayList<Engineer> enList = new ArrayList<Engineer>();
         try{
             Conn cnn = new Conn();
             String sql = "select engineerid, firstname, lastname "
                     + "from engineer "
                     + "where engineerid not in "
                                                + "(select e.engineerid "
                                                + "from engineer e, assignment a "
                                                + "where a.engineerid = e.engineerid "
                                                + "and a.taskid='"+taskid+"' " 
                                                + "and a.projectid='"+projectid+"') "
                     + "and edelete = 0 "
                     + "and engineerid <>'"+userid+"'";
             ResultSet rs = cnn.exec(sql);
             while(rs.next()){
                 Engineer en = new Engineer();
                 en.setEngineerID(rs.getString("engineerid"));
                 en.setFirstName(rs.getString("firstname"));
                 en.setLastName(rs.getString("lastname"));
                 enList.add(en);
             }
         }
         catch(Exception e){
             e.getMessage();
         }
         return enList;
     }
     
     /**
      * Delete Assigned Engineer from Assignment Table
      */
     public int updateAssignEngineer (String projectid, String taskid, String engineerid){
         int row = 0;
         try{
             Conn cnn = new Conn();
             String proc = "call Delete_EngineerAssigned(?,?,?)";
             PreparedStatement prstm = cnn.getPrepareStatement(proc);
             prstm.setString(1, projectid);
             prstm.setString(2, taskid);
             prstm.setString(3, engineerid);
             row = prstm.executeUpdate();
         }catch(Exception e){
             e.getMessage();
         }
         return row;
     }
     
     /**
      * Insert Engineer to Task
      */
     public int addAssignEngineer (String projectid, String taskid, String engineerid){
         int row = 0;
         try{
             Conn cnn = new Conn();
             String proc = "call Insert_EngineerAssigned(?,?,?)";
             PreparedStatement prstm = cnn.getPrepareStatement(proc);
             prstm.setString(1, projectid);
             prstm.setString(2, taskid);
             prstm.setString(3, engineerid);
             row = prstm.executeUpdate();
         }catch(Exception e){
             e.getMessage();
         }
         return row;
     }
     
     /**
      * Update Assign Info
      * @return number of row effected
      * @params planstartdate, plansenddate, startdate, enddate
      */
     public String  updateAssignInfo (String projectid, String taskid, String planStartDate, String planEndDate, String startDate, String endDate, String completeRatio){
         //int row=0;
         String msg = "";
         try{
             Conn cnn = new Conn();
             
             String proc = "call Update_Progress(?,?,?,?,?,?,?,?)";
             CallableStatement prstm = cnn.conn.prepareCall(proc);
             prstm.setString(1, projectid);
             prstm.setString(2, taskid);
             prstm.setString(3, planStartDate);
             prstm.setString(4, planEndDate);
             prstm.setString(5, startDate);
             prstm.setString(6, endDate);
             prstm.setInt(7, Integer.parseInt(completeRatio));
             prstm.registerOutParameter(8,Types.VARCHAR);
             prstm.executeUpdate();
             msg = prstm.getString("Message");
             if(msg.equals("1"))
             {
                 int count = 0;
                 float projRatio =0;
                 String query = "select completeRatio from assignment where projectID = '"+projectid+"' group by taskid, completeratio";
                 ResultSet rs= cnn.exec(query);
                 while(rs.next())
                 {
                     projRatio = projRatio + rs.getInt("completeRatio");
                     count ++;                     
                 }
                 projRatio = projRatio/count;
                 
                 String query1 = "Update project set completeRatio = "+projRatio+" where projectID = '"+projectid+"'";
                 cnn.upd(query1);
                 
                 if(projRatio == 100)
                 {
                     String query2 = "Update project set EndDate = '"+endDate+"' where projectID = '"+projectid+"'";
                        cnn.upd(query2);
                 }
             }
         }catch(Exception e){
             e.getMessage();
         }
         return msg;
     }
     
     /**
      * 
      */
     public ArrayList<Project> viewProjectProgress (String userid) throws SQLException {
        ArrayList<Project> arrPro = new ArrayList<Project>();
        try {
               Conn cnn = new Conn();
//            Engineer en = new Engineer();
//            //String userid = getUserid();
//            String select_engineer = "Select * from engineer where engineerid='" + userid + "'";
//            ResultSet rsEngineer = cnn.exec(select_engineer);
//            while (rsEngineer.next()) {
//                en.setEngineerID(userid);
//                en.setFirstName(rsEngineer.getString("firstname"));
//                en.setLastName(rsEngineer.getString("lastname"));
//                en.setMajor(rsEngineer.getString("major"));
//            }

//            String select_project = "Select distinct p.ProjectID, p.projectname, p.teamleaderid, p.description, p.planguage from project p, engineer e"
//                    + " Where p.teamleaderid ='" + userid + "'";

            String proc ="call View_ParticipatedProject_ByEnID(?)";
            PreparedStatement prstm = cnn.getPrepareStatement(proc);
            prstm.setString(1, userid);
            ResultSet rsProject = prstm.executeQuery();
            //ResultSet rsProject = cnn.exec(select_project);
            if (rsProject != null) {
                while (rsProject.next()) {
                    Project pro = new Project();
                    pro.setProjectID(rsProject.getString("projectid"));
                    pro.setProjectName(rsProject.getString("projectname"));
                    pro.setLanguage(rsProject.getString("planguage"));
                    pro.setDescription(rsProject.getString("description"));
                    
                    Engineer en = new Engineer();
                    en.setEngineerID(rsProject.getString("teamleaderid"));
                    en.setFirstName(rsProject.getString("firstname"));
                    en.setLastName(rsProject.getString("lastname"));
                    pro.setTeamLeader(en);
                    
                    pro.setCompleteRatio(rsProject.getInt("completeratio")+"");
                    //pro.setProject(pro);
                    arrPro.add(pro);
                }

            } else {
                arrPro = null;
            }
        } catch (Exception e) {
            e.getMessage();
        }
        return arrPro;
    }
     
    /**
     * @return the taskid
     */
    public String getTaskid() {
        return taskid;
    }

    /**
     * @param taskid the taskid to set
     */
    public void setTaskid(String taskid) {
        this.taskid = taskid;
    }

    /**
     * @return the engineerid
     */
    public String getEngineerid() {
        return engineerid;
    }

    /**
     * @param engineerid the engineerid to set
     */
    public void setEngineerid(String engineerid) {
        this.engineerid = engineerid;
    }

    /**
     * @return the engineerid1
     */
    public String getEngineerid1() {
        return engineerid1;
    }

    /**
     * @param engineerid1 the engineerid1 to set
     */
    public void setEngineerid1(String engineerid1) {
        this.engineerid1 = engineerid1;
    }

    /**
     * @return the planStartDate
     */
    public String getPlanStartDate() {
        return planStartDate;
    }

    /**
     * @param planStartDate the planStartDate to set
     */
    public void setPlanStartDate(String planStartDate) {
        this.planStartDate = planStartDate;
    }

    /**
     * @return the planEndDate
     */
    public String getPlanEndDate() {
        return planEndDate;
    }

    /**
     * @param planEndDate the planEndDate to set
     */
    public void setPlanEndDate(String planEndDate) {
        this.planEndDate = planEndDate;
    }

    /**
     * @return the startDate
     */
    public String getStartDate() {
        return startDate;
    }

    /**
     * @param startDate the startDate to set
     */
    public void setStartDate(String startDate) {
        this.startDate = startDate;
    }

    /**
     * @return the endDate
     */
    public String getEndDate() {
        return endDate;
    }

    /**
     * @param endDate the endDate to set
     */
    public void setEndDate(String endDate) {
        this.endDate = endDate;
    }

    /**
     * @return the completeRatio
     */
    public String getCompleteRatio() {
        return completeRatio;
    }

    /**
     * @param completeRatio the completeRatio to set
     */
    public void setCompleteRatio(String completeRatio) {
        this.completeRatio = completeRatio;
    }
}
